package com.atstudy.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @version v1.0
 * @description:
 * @author: admin
 * @date: 2023/1/11 10:29
 * @copyright: 博为峰
 */
public class JdbcDemo04 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBUtils.getConn();
            //设置非自动提交
            conn.setAutoCommit(false);
            //插入班级表
            Cla cla = new Cla(1, "119期", 119, new Date());
            int claId = insert(conn, cla);
            //插入学生表
            Student student = new Student("张云雷", "男", "29期", 30, 65, claId);
            insert(conn, student);
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    private static int insert(Connection conn, Cla cla) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int id = -1;
        try {
            //插入班级
            preparedStatement = conn.prepareStatement("insert into cla (name,num,create_time)values(?,?,?)");
            preparedStatement.setString(1, cla.getName());
            preparedStatement.setInt(2, cla.getNum());
            preparedStatement.setTimestamp(3, DateUtils.getTimestamp());
            System.out.println(preparedStatement);
            int i = preparedStatement.executeUpdate();
            //获取主键值（只适用于自增）
            preparedStatement = conn.prepareStatement("select last_insert_id()");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                id = resultSet.getInt(1);
            }
//            return id;
        } catch (SQLException throwables) {
            throw new RuntimeException("插入班级异常");
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return id;
    }

    private static void insert(Connection connection, Student student) {
        try (
                PreparedStatement preparedStatement = connection.prepareStatement("insert into stu(name,sex,cla_name,age,score,cla_id) values (?,?,?,?,?,?)");
        ) {
            System.out.println(1 / 0);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setString(3, student.getClaName());
            preparedStatement.setInt(4, student.getAge());
            preparedStatement.setInt(5, student.getScore());
            preparedStatement.setInt(6, student.getClaId());
            int i = preparedStatement.executeUpdate();
            System.out.println("i = " + i);
        } catch (SQLException throwables) {
            throw new RuntimeException("插入学生异常");
        }
    }
}
